named-placeholders

What is named-placeholders?

The named-placeholders npm package is used to convert SQL query strings with named placeholders into a format that can be used with parameterized queries. This is particularly useful for preventing SQL injection attacks and making SQL queries more readable and maintainable.

What are named-placeholders's main functionalities?

Convert named placeholders to positional placeholders

This feature allows you to convert a SQL query with named placeholders into a query with positional placeholders, which can then be executed using a database client that supports parameterized queries.

const named = require('named-placeholders')();
const query = 'SELECT * FROM users WHERE name = :name AND age = :age';
const params = { name: 'John', age: 30 };
const [sql, values] = named(query, params);
console.log(sql); // 'SELECT * FROM users WHERE name = ? AND age = ?'
console.log(values); // ['John', 30]

Support for repeated placeholders

This feature allows you to use the same named placeholder multiple times in a query. The named-placeholders package will correctly replace all instances with the appropriate positional placeholders.

const named = require('named-placeholders')();
const query = 'SELECT * FROM users WHERE name = :name OR nickname = :name';
const params = { name: 'John' };
const [sql, values] = named(query, params);
console.log(sql); // 'SELECT * FROM users WHERE name = ? OR nickname = ?'
console.log(values); // ['John', 'John']

Support for array parameters

This feature allows you to use arrays as parameters in your SQL queries. The named-placeholders package will expand the array into the appropriate number of positional placeholders.

const named = require('named-placeholders')();
const query = 'SELECT * FROM users WHERE id IN (:ids)';
const params = { ids: [1, 2, 3] };
const [sql, values] = named(query, params);
console.log(sql); // 'SELECT * FROM users WHERE id IN (?, ?, ?)'
console.log(values); // [1, 2, 3]

Other packages similar to named-placeholders

mysql2

The mysql2 package is a MySQL client for Node.js that supports named placeholders directly in its query method. It provides similar functionality to named-placeholders but is specific to MySQL databases.

pg-promise

The pg-promise package is a PostgreSQL client for Node.js that supports named parameters in queries. It offers similar functionality to named-placeholders but is tailored for PostgreSQL databases.

sequelize

Sequelize is an ORM for Node.js that supports named replacements in raw SQL queries. It provides a higher-level abstraction for database interactions, including support for named placeholders.

README

named-placeholders

compiles "select foo where foo.id = :bar and foo.baz < :baz" into "select foo where foo.id = ? and foo.baz < ?" + ["bar", "baz"]

usage

npm install named-placeholders

see this mysql2 discussion

var mysql = require('mysql');
var toUnnamed = require('named-placeholders')();

var q = toUnnamed('select 1+:test', { test: 123});
mysql.createConnection().query(q[0], q[1]);

credits

parser is based on @mscdex code of his excellent node-mariasql library